{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d7fb2169",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "#导入相关库\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Line, Bar\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86549490",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "#获取数据\n",
    "def read_data(table):\n",
    "    sql = \"SELECT * FROM \" + table\n",
    "    # 设置openGauss数据库版本信息\n",
    "    from sqlalchemy.dialects.postgresql.base import PGDialect\n",
    "    PGDialect._get_server_version_info = lambda *args: (9, 2)\n",
    "    userName = 'myroot'\n",
    "    password = 'myroot_123'\n",
    "    dbHost = '10.10.74.207'\n",
    "    dbPort = 15400\n",
    "    dbName = 'app'\n",
    "    DB_CONNECT = f'postgresql://{userName}:{password}@{dbHost}:{dbPort}/{dbName}'\n",
    "    conn_new = sqlalchemy.create_engine(DB_CONNECT)\n",
    "    if conn_new:\n",
    "        print(\"连接数据库成功\")\n",
    "    df = pd.read_sql(sql, conn_new)\n",
    "    print(df.columns)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "97bbb16f",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "连接数据库成功\n",
      "Index(['id', 'selfid', 'name', 'addr'], dtype='object')\n",
      "连接数据库成功\n",
      "Index(['id', 'companyid', 'name', 'equipmentnumber'], dtype='object')\n",
      "连接数据库成功\n",
      "Index(['id', 'datetime', 'equipmentnumber', 'worktime', 'standbytime'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "# 读取公司信息CompanyInfos，设备信息InstallInfos，运行时间信息TimeInfos，删除id列\n",
    "CompanyInfos = read_data('companyinfos')\n",
    "CompanyInfos.drop(columns='id', inplace=True)\n",
    "InstallInfos = read_data('installinfos')\n",
    "InstallInfos.drop(columns='id', inplace=True)\n",
    "TimeInfos = read_data('timeinfos')\n",
    "TimeInfos.drop(columns='id', inplace=True)\n",
    "# 将表中的数据'worktime','standbytime'转换成浮点数\n",
    "TimeInfos['worktime'] = TimeInfos['worktime'].astype('float64')\n",
    "TimeInfos['standbytime'] = TimeInfos['standbytime'].astype('float64')\n",
    "# 计算所需的 每天停机时间:'stoptime',开机率'startrate',工作效率:'efficiency'\n",
    "TimeInfos['stoptime'] = round(24 - (TimeInfos['worktime'] + TimeInfos['standbytime']), 2)\n",
    "TimeInfos['startrate'] = round(((TimeInfos['worktime'] + TimeInfos['standbytime']) / 24) * 100, 2)\n",
    "TimeInfos['efficiency'] = round(TimeInfos['worktime'] / (TimeInfos['worktime'] + TimeInfos['standbytime']) * 100, 2)\n",
    "TimeInfos.fillna(0, inplace=True)\n",
    "# 获取TimeInfos表中的最大日期，并筛选出该日期对应的数据保存在day_timeinfos变量中\n",
    "date = TimeInfos['datetime'].max()\n",
    "day_timeinfos = TimeInfos[TimeInfos['datetime'] == date]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1048c6e2",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "equipMentNumber = \"DNKLY4032407-A\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2b12f745",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def operating_data(equipMentNumber):\n",
    "    date = (datetime.datetime.strptime(TimeInfos['datetime'].max(), '%Y-%m-%d') + datetime.timedelta(\n",
    "        days=-30)).strftime('%Y-%m-%d')\n",
    "    einfo = TimeInfos[(TimeInfos['equipmentnumber'] == equipMentNumber) & (TimeInfos['datetime'] > date)]\n",
    "    return einfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "72da093d",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "einfo = operating_data(equipMentNumber)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "494cc2d2",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"fedc4228125742aea492b0fb7af269a0\" style=\"width:100%; height:380px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_fedc4228125742aea492b0fb7af269a0 = echarts.init(\n",
       "                    document.getElementById('fedc4228125742aea492b0fb7af269a0'), 'white', {renderer: 'canvas'});\n",
       "                var option_fedc4228125742aea492b0fb7af269a0 = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#33ff99\",\n",
       "        \"#33ffff\",\n",
       "        \"#9999ff\",\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u8fd0\\u884c\\u65f6\\u957f\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                3.03\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"30%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        },\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u5f85\\u673a\\u65f6\\u957f\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                3.43\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"30%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        },\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u505c\\u673a\\u65f6\\u957f\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                17.54\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"30%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u8fd0\\u884c\\u65f6\\u957f\",\n",
       "                \"\\u5f85\\u673a\\u65f6\\u957f\",\n",
       "                \"\\u505c\\u673a\\u65f6\\u957f\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"\\u8fd0\\u884c\\u65f6\\u957f\": true,\n",
       "                \"\\u5f85\\u673a\\u65f6\\u957f\": true,\n",
       "                \"\\u505c\\u673a\\u65f6\\u957f\": true\n",
       "            },\n",
       "            \"show\": true,\n",
       "            \"right\": \"0\",\n",
       "            \"top\": \"0\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 10,\n",
       "            \"itemHeight\": 14,\n",
       "            \"textStyle\": {\n",
       "                \"color\": \"black\",\n",
       "                \"fontSize\": 10\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"showContent\": true,\n",
       "        \"alwaysShowContent\": false,\n",
       "        \"showDelay\": 0,\n",
       "        \"hideDelay\": 100,\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0,\n",
       "        \"padding\": 5\n",
       "    },\n",
       "    \"xAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"axisLine\": {\n",
       "                \"show\": true,\n",
       "                \"onZero\": true,\n",
       "                \"onZeroAxisIndex\": 0,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\",\n",
       "                    \"color\": \"black\"\n",
       "                }\n",
       "            },\n",
       "            \"axisLabel\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"color\": \"black\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            },\n",
       "            \"data\": [\n",
       "                \"DNKLY4032407-A\"\n",
       "            ]\n",
       "        }\n",
       "    ],\n",
       "    \"yAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"axisLine\": {\n",
       "                \"show\": true,\n",
       "                \"onZero\": true,\n",
       "                \"onZeroAxisIndex\": 0,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\",\n",
       "                    \"color\": \"black\"\n",
       "                }\n",
       "            },\n",
       "            \"axisLabel\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"color\": \"black\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u8bbe\\u5907\\u72b6\\u6001\",\n",
       "            \"subtext\": \"\\u8fd130\\u5929\\u5e73\\u5747\\u503c\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"textStyle\": {\n",
       "                \"color\": \"white\"\n",
       "            }\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_fedc4228125742aea492b0fb7af269a0.setOption(option_fedc4228125742aea492b0fb7af269a0);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x7fba230f5630>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 设备运行状态\n",
    "bar = Bar(init_opts=opts.InitOpts(width=\"100%\", height='380px'))\n",
    "bar.add_xaxis([equipMentNumber])\n",
    "bar.add_yaxis(\"运行时长\", [round(einfo['worktime'].mean(), 2)], color='#9999ff')\n",
    "bar.add_yaxis(\"待机时长\", [round(einfo['standbytime'].mean(), 2)], color='#33ffff')\n",
    "bar.add_yaxis(\"停机时长\", [round(einfo['stoptime'].mean(), 2)], color='#33ff99')\n",
    "bar.set_global_opts(\n",
    "    title_opts=opts.TitleOpts(title='设备状态', subtitle='近30天平均值', title_textstyle_opts={\"color\": \"white\"}),\n",
    "    legend_opts=opts.LegendOpts(\n",
    "        is_show=True, item_width=10, pos_right='0', pos_top='0',\n",
    "        textstyle_opts=opts.TextStyleOpts(color='black', font_size=10),\n",
    "    ),\n",
    "    xaxis_opts=opts.AxisOpts(\n",
    "        axislabel_opts=opts.LabelOpts(color=\"black\"),\n",
    "        axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color=\"black\"))\n",
    "    ),\n",
    "    yaxis_opts=opts.AxisOpts(\n",
    "        axislabel_opts=opts.LabelOpts(color=\"black\"),\n",
    "        axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color=\"black\"))\n",
    "    )\n",
    ")\n",
    "bar.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "90554d6b",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"108c79b0c7f44a3bb9fcc5734c7266b9\" style=\"width:100%; height:380px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_108c79b0c7f44a3bb9fcc5734c7266b9 = echarts.init(\n",
       "                    document.getElementById('108c79b0c7f44a3bb9fcc5734c7266b9'), 'white', {renderer: 'canvas'});\n",
       "                var option_108c79b0c7f44a3bb9fcc5734c7266b9 = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#33ffff\",\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"line\",\n",
       "            \"connectNulls\": false,\n",
       "            \"symbolSize\": 4,\n",
       "            \"showSymbol\": true,\n",
       "            \"smooth\": false,\n",
       "            \"clip\": true,\n",
       "            \"step\": false,\n",
       "            \"data\": [\n",
       "                [\n",
       "                    \"11-23\",\n",
       "                    36.25\n",
       "                ],\n",
       "                [\n",
       "                    \"11-25\",\n",
       "                    30.42\n",
       "                ],\n",
       "                [\n",
       "                    \"11-26\",\n",
       "                    29.17\n",
       "                ],\n",
       "                [\n",
       "                    \"12-22\",\n",
       "                    11.79\n",
       "                ]\n",
       "            ],\n",
       "            \"hoverAnimation\": true,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"lineStyle\": {\n",
       "                \"show\": true,\n",
       "                \"width\": 1,\n",
       "                \"opacity\": 1,\n",
       "                \"curveness\": 0,\n",
       "                \"type\": \"solid\"\n",
       "            },\n",
       "            \"areaStyle\": {\n",
       "                \"opacity\": 0\n",
       "            },\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 0\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"\": true\n",
       "            },\n",
       "            \"show\": true,\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 25,\n",
       "            \"itemHeight\": 14\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"showContent\": true,\n",
       "        \"alwaysShowContent\": false,\n",
       "        \"showDelay\": 0,\n",
       "        \"hideDelay\": 100,\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0,\n",
       "        \"padding\": 5\n",
       "    },\n",
       "    \"xAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"axisLine\": {\n",
       "                \"show\": true,\n",
       "                \"onZero\": true,\n",
       "                \"onZeroAxisIndex\": 0,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\",\n",
       "                    \"color\": \"black\"\n",
       "                }\n",
       "            },\n",
       "            \"axisLabel\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"color\": \"black\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            },\n",
       "            \"data\": [\n",
       "                \"11-23\",\n",
       "                \"11-25\",\n",
       "                \"11-26\",\n",
       "                \"12-22\"\n",
       "            ]\n",
       "        }\n",
       "    ],\n",
       "    \"yAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"axisLine\": {\n",
       "                \"show\": true,\n",
       "                \"onZero\": true,\n",
       "                \"onZeroAxisIndex\": 0,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\",\n",
       "                    \"color\": \"black\"\n",
       "                }\n",
       "            },\n",
       "            \"axisLabel\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"color\": \"black\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u8bbe\\u5907\\u5f00\\u673a\\u7387(%)\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"textStyle\": {\n",
       "                \"color\": \"black\"\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"dataZoom\": {\n",
       "        \"show\": true,\n",
       "        \"type\": \"slider\",\n",
       "        \"realtime\": true,\n",
       "        \"start\": 0,\n",
       "        \"end\": 60,\n",
       "        \"orient\": \"horizontal\",\n",
       "        \"zoomLock\": false,\n",
       "        \"filterMode\": \"filter\"\n",
       "    }\n",
       "};\n",
       "                chart_108c79b0c7f44a3bb9fcc5734c7266b9.setOption(option_108c79b0c7f44a3bb9fcc5734c7266b9);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x7fba235a8978>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 设备开机率\n",
    "einfo = operating_data(equipMentNumber)\n",
    "line = Line(init_opts=opts.InitOpts(width='100%', height='380px'))\n",
    "line.add_xaxis([i[-5:] for i in einfo['datetime'].tolist()])\n",
    "line.add_yaxis(\"\", einfo['startrate'].tolist(), color='#33ffff')\n",
    "line.set_global_opts(\n",
    "    title_opts=opts.TitleOpts(title=\"设备开机率(%)\", title_textstyle_opts={\"color\": \"black\"}),\n",
    "    datazoom_opts=opts.DataZoomOpts(range_start=0, range_end=60),\n",
    "    xaxis_opts=opts.AxisOpts(\n",
    "        axislabel_opts=opts.LabelOpts(color=\"black\"),\n",
    "        axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color=\"black\"))\n",
    "    )\n",
    "    , yaxis_opts=opts.AxisOpts(\n",
    "        axislabel_opts=opts.LabelOpts(color=\"black\"),\n",
    "        axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color=\"black\"))\n",
    "    )\n",
    ")\n",
    "line.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c9e8ba96",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def equipments_infos(equipMentNumber):\n",
    "    #根据设备编号获取设备名字\n",
    "    name = InstallInfos[InstallInfos['equipmentnumber'] == equipMentNumber]['name'].tolist()[0]\n",
    "    #根据设备名字获取所有同名设备\n",
    "    equipments = InstallInfos[InstallInfos.name == name]\n",
    "    #获取timeinfos中最新日期往前倒推30天的日期序列\n",
    "    date = (datetime.datetime.strptime(TimeInfos['datetime'].max(), '%Y-%m-%d') + datetime.timedelta(\n",
    "        days=-30)).strftime('%Y-%m-%d')\n",
    "    tinfos = TimeInfos[TimeInfos.datetime > date]\n",
    "    #获取该日期序列中所有同名设备的运行统计数据\n",
    "    einfos = pd.merge(equipments, tinfos, on='equipmentnumber', how='inner')\n",
    "    einfos.drop(columns=['companyid', 'name', 'datetime'], inplace=True)\n",
    "    einfos = round(einfos.groupby('equipmentnumber', as_index=False).mean(), 2)\n",
    "    einfos['srank'] = einfos['startrate'].rank(method='dense', ascending=False)\n",
    "    einfos.fillna(0, inplace=True)\n",
    "    einfos['score'] = einfos['srank']\n",
    "    return einfos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a37c38d9",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "        <div id=\"78a7c68fe51b4b55b15392a4a3b6d3e6\" style=\"width:100%; height:770px;\"></div>\n",
       "\n",
       "<script>\n",
       "        require(['echarts'], function(echarts) {\n",
       "                var chart_78a7c68fe51b4b55b15392a4a3b6d3e6 = echarts.init(\n",
       "                    document.getElementById('78a7c68fe51b4b55b15392a4a3b6d3e6'), 'white', {renderer: 'canvas'});\n",
       "                var option_78a7c68fe51b4b55b15392a4a3b6d3e6 = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#33ff99\",\n",
       "        \"#33ffff\",\n",
       "        \"#9999ff\",\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u8fd0\\u884c\\u65f6\\u957f\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                6.12,\n",
       "                5.5,\n",
       "                7.28,\n",
       "                2.89,\n",
       "                0.27\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"stack\": \"stack1\",\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"30%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": false,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"rippleEffect\": {\n",
       "                \"show\": true,\n",
       "                \"brushType\": \"stroke\",\n",
       "                \"scale\": 2.5,\n",
       "                \"period\": 4\n",
       "            }\n",
       "        },\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u5f85\\u673a\\u65f6\\u957f\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                4.04,\n",
       "                4.08,\n",
       "                1.57,\n",
       "                5.88,\n",
       "                8.11\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"stack\": \"stack1\",\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"30%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": false,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"rippleEffect\": {\n",
       "                \"show\": true,\n",
       "                \"brushType\": \"stroke\",\n",
       "                \"scale\": 2.5,\n",
       "                \"period\": 4\n",
       "            }\n",
       "        },\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u505c\\u673a\\u65f6\\u957f\",\n",
       "            \"legendHoverLink\": true,\n",
       "            \"data\": [\n",
       "                13.84,\n",
       "                14.42,\n",
       "                15.15,\n",
       "                15.23,\n",
       "                15.62\n",
       "            ],\n",
       "            \"showBackground\": false,\n",
       "            \"stack\": \"stack1\",\n",
       "            \"barMinHeight\": 0,\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"barGap\": \"30%\",\n",
       "            \"large\": false,\n",
       "            \"largeThreshold\": 400,\n",
       "            \"seriesLayoutBy\": \"column\",\n",
       "            \"datasetIndex\": 0,\n",
       "            \"clip\": true,\n",
       "            \"zlevel\": 0,\n",
       "            \"z\": 2,\n",
       "            \"label\": {\n",
       "                \"show\": false,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"rippleEffect\": {\n",
       "                \"show\": true,\n",
       "                \"brushType\": \"stroke\",\n",
       "                \"scale\": 2.5,\n",
       "                \"period\": 4\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u8fd0\\u884c\\u65f6\\u957f\",\n",
       "                \"\\u5f85\\u673a\\u65f6\\u957f\",\n",
       "                \"\\u505c\\u673a\\u65f6\\u957f\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"\\u8fd0\\u884c\\u65f6\\u957f\": true,\n",
       "                \"\\u5f85\\u673a\\u65f6\\u957f\": true,\n",
       "                \"\\u505c\\u673a\\u65f6\\u957f\": true\n",
       "            },\n",
       "            \"show\": true,\n",
       "            \"right\": \"5\",\n",
       "            \"top\": \"0\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"itemWidth\": 10,\n",
       "            \"itemHeight\": 14,\n",
       "            \"textStyle\": {\n",
       "                \"color\": \"white\",\n",
       "                \"fontSize\": 10\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"showContent\": true,\n",
       "        \"alwaysShowContent\": false,\n",
       "        \"showDelay\": 0,\n",
       "        \"hideDelay\": 100,\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0,\n",
       "        \"padding\": 5\n",
       "    },\n",
       "    \"xAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"axisLine\": {\n",
       "                \"show\": true,\n",
       "                \"onZero\": true,\n",
       "                \"onZeroAxisIndex\": 0,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\",\n",
       "                    \"color\": \"black\"\n",
       "                }\n",
       "            },\n",
       "            \"axisLabel\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"color\": \"black\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 24,\n",
       "            \"min\": 0,\n",
       "            \"max\": 24,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"yAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"axisLine\": {\n",
       "                \"show\": true,\n",
       "                \"onZero\": true,\n",
       "                \"onZeroAxisIndex\": 0,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\",\n",
       "                    \"color\": \"black\"\n",
       "                }\n",
       "            },\n",
       "            \"axisLabel\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"color\": \"black\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"show\": true,\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            },\n",
       "            \"data\": [\n",
       "                \"DNKLY4032764-A\",\n",
       "                \"DNKLY4032164-A\",\n",
       "                \"DNKLY4032432-A\",\n",
       "                \"DNKLY4032151-A\",\n",
       "                \"DNKLY4032152-A\"\n",
       "            ]\n",
       "        }\n",
       "    ],\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u5367\\u5f0f\\u8f66\\u5e8a\\u8bbe\\u5907\\u72b6\\u6001TOP5\",\n",
       "            \"padding\": 5,\n",
       "            \"itemGap\": 10,\n",
       "            \"textStyle\": {\n",
       "                \"color\": \"black\"\n",
       "            }\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "                chart_78a7c68fe51b4b55b15392a4a3b6d3e6.setOption(option_78a7c68fe51b4b55b15392a4a3b6d3e6);\n",
       "        });\n",
       "    </script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x7fba235d67b8>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name = InstallInfos[InstallInfos['equipmentnumber'] == equipMentNumber].name.tolist()[0]\n",
    "einfos = equipments_infos(equipMentNumber)\n",
    "etop = einfos.sort_values(by='score')[0:5]\n",
    "c = Bar(init_opts=opts.InitOpts(width=\"100%\", height='770px'))\n",
    "c.add_xaxis(etop['equipmentnumber'].tolist())\n",
    "c.add_yaxis('运行时长', etop['worktime'].tolist(), color='#9999ff', stack=\"stack1\")\n",
    "c.add_yaxis('待机时长', etop['standbytime'].tolist(), color='#33ffff', stack=\"stack1\")\n",
    "c.add_yaxis('停机时长', etop['stoptime'].tolist(), color='#33ff99', stack=\"stack1\")\n",
    "c.set_global_opts(title_opts=opts.TitleOpts(name + '设备状态TOP5', title_textstyle_opts={\"color\": \"black\"}),\n",
    "    legend_opts=opts.LegendOpts(\n",
    "        is_show=True, item_width=10, pos_right='5', pos_top='0',\n",
    "        textstyle_opts=opts.TextStyleOpts(color='white', font_size=10),\n",
    "    ),\n",
    "    xaxis_opts=opts.AxisOpts(\n",
    "        split_number=24, min_=0, max_=24,\n",
    "        axislabel_opts=opts.LabelOpts(color=\"black\"),\n",
    "        axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color=\"black\"))\n",
    "    ),\n",
    "    yaxis_opts=opts.AxisOpts(\n",
    "        axislabel_opts=opts.LabelOpts(color=\"black\"),\n",
    "        axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(color=\"black\"))\n",
    "    )\n",
    ")\n",
    "c.reversal_axis()\n",
    "c.set_series_opts(label_opts=opts.LabelOpts(is_show=False))\n",
    "c.render_notebook()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
